import csv
import sys
import logging
from pathlib import Path
from typing import Dict, Any, List
from sqlalchemy.exc import SQLAlchemyError

# 添加项目根目录到Python路径
sys.path.append(str(Path(__file__).resolve().parent.parent.parent))

from apps.tower.models import TowerV1
from core.globals import sync_db
from sqlalchemy.orm import Session

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.StreamHandler(),
        logging.FileHandler(Path(__file__).resolve().parent.parent.parent / 'import_csv.log')
    ]
)
logger = logging.getLogger(__name__)

def import_csv_to_towerv1(csv_file_path: str) -> Dict[str, Any]:
    """
    从CSV文件导入数据到TowerV1表
    
    Args:
        csv_file_path: CSV文件路径
        
    Returns:
        Dict: 导入结果统计信息
    """
    result = {
        "total_rows": 0,
        "imported_rows": 0,
        "failed_rows": 0,
        "errors": []
    }
    
    try:
        csv_path = Path(csv_file_path)
        if not csv_path.exists():
            error_msg = f"CSV文件不存在: {csv_file_path}"
            logger.error(error_msg)
            result["errors"].append(error_msg)
            return result
        
        logger.info(f"开始导入数据从: {csv_file_path}")
        
        # 手动定义TowerV1模型的字段别名映射
        # 基于TowerV1模型的定义，使用Field中的alias作为映射键
        tower_fields = {
            "序号": "serial_number",
            "基站编码": "base_station_code",
            "镇区": "town",
            "片区": "area",
            "铁塔站名": "tower_name",
            "建设站名": "construction_name",
            "建设类型": "construction_type",
            "立项编码": "project_approval_code",
            "改造类型": "renovation_type",
            "是否共建": "is_joint_construction",
            "专项批次": "special_batch",
            "是否改造转新建或新建转改造": "is_transform",
            "属性变更日期": "attribute_change_date",
            "存量运营商": "existing_operators",
            "需求运营商": "demanding_operators",
            "移动规划站点名称": "mobile_planned_site_name",
            "移动需求批次": "mobile_demand_batch",
            "移动建设属性": "mobile_construction_attribute",
            "移动专项批次": "mobile_special_batch",
            "电信需求批次": "telecom_demand_batch",
            "电信规划站点名称": "telecom_planned_site_name",
            "联通需求批次": "unicom_demand_batch",
            "联通规划站点名称": "unicom_planned_site_name",
            "需求规划经度": "demand_longitude",
            "需求规划纬度": "demand_latitude",
            "实际建设经度": "actual_longitude",
            "实际建设纬度": "actual_latitude",
            "公共属性经度": "public_property_longitude",
            "公共属性纬度": "public_property_latitude",
            "详细地址": "detailed_address",
            "谈点人及联系方式": "contact_person"
        }
        
        logger.info(f"已设置TowerV1模型字段映射，共 {len(tower_fields)} 个字段")
        
        with open(csv_file_path, 'r', encoding='utf-8-sig') as csv_file:
            csv_reader = csv.DictReader(csv_file)
            rows_to_import = []
            
            for row_idx, row in enumerate(csv_reader, 1):
                result["total_rows"] += 1
                
                # 创建TowerV1对象的数据字典
                tower_data = {}
                
                # 只提取TowerV1模型中存在的字段
                for csv_field, csv_value in row.items():
                    # 去除字段名两端的空白字符
                    csv_field = csv_field.strip()
                    csv_value = csv_value.strip() if csv_value is not None else None
                    
                    # 如果CSV字段名匹配TowerV1的别名，就添加到数据字典中
                    if csv_field in tower_fields:
                        field_name = tower_fields[csv_field]
                        # 处理空字符串，转换为None
                        tower_data[field_name] = csv_value if csv_value else None
                
                # 避免重复的实际建设经纬度字段问题（模型中似乎有重复定义）
                # 确保只保留一个值
                if "actual_longitude" in tower_data:
                    pass
                
                try:
                    # 验证数据并创建实例
                    tower_instance = TowerV1(**tower_data)
                    rows_to_import.append(tower_instance)
                except Exception as e:
                    logger.error(f"第{row_idx}行数据创建失败: {str(e)}")
                    result["failed_rows"] += 1
                    result["errors"].append(f"第{row_idx}行数据创建失败: {str(e)}")
                    continue
                
                # 每100行插入一次，避免内存占用过大
                if len(rows_to_import) >= 100:
                    logger.info(f"准备导入第 {result['total_rows']-99} 到第 {result['total_rows']} 行数据")
                    # 使用SQLAlchemy的Session类直接通过engine创建会话
                    session = Session(sync_db.engine)
                    batch_imported = 0
                    batch_failed = 0
                    try:
                        # 逐个添加记录，便于定位具体失败的行
                        for idx, tower_instance in enumerate(rows_to_import, 1):
                            try:
                                session.add(tower_instance)
                                batch_imported += 1
                            except Exception as e:
                                batch_failed += 1
                                error_row_idx = result['total_rows']-99 + idx - 1
                                error_msg = f"第{error_row_idx}行导入失败: {str(e)}"
                                logger.error(error_msg)
                                result["errors"].append(error_msg)
                                continue
                        
                        # 提交成功的记录
                        if batch_imported > 0:
                            session.commit()
                            result["imported_rows"] += batch_imported
                            logger.info(f"成功导入 {batch_imported} 条记录")
                        
                        if batch_failed > 0:
                            result["failed_rows"] += batch_failed
                            logger.warning(f"{batch_failed} 条记录导入失败")
                        
                        rows_to_import = []
                    except Exception as e:
                        session.rollback()
                        error_msg = f"批量导入过程发生错误: {str(e)}"
                        logger.error(error_msg)
                        result["errors"].append(error_msg)
                        rows_to_import = []
                    finally:
                        session.close()
            
            # 导入剩余的数据行
            if rows_to_import:
                logger.info(f"准备导入剩余的 {len(rows_to_import)} 行数据")
                # 使用SQLAlchemy的Session类直接通过engine创建会话
                session = Session(sync_db.engine)
                batch_imported = 0
                batch_failed = 0
                try:
                    # 逐个添加剩余记录
                    for idx, tower_instance in enumerate(rows_to_import, 1):
                        try:
                            session.add(tower_instance)
                            batch_imported += 1
                        except Exception as e:
                            batch_failed += 1
                            error_row_idx = result['total_rows'] - len(rows_to_import) + idx
                            error_msg = f"剩余数据中第{error_row_idx}行导入失败: {str(e)}"
                            logger.error(error_msg)
                            result["errors"].append(error_msg)
                            continue
                    
                    # 提交成功的记录
                    if batch_imported > 0:
                        session.commit()
                        result["imported_rows"] += batch_imported
                        logger.info(f"成功导入 {batch_imported} 条记录")
                    
                    if batch_failed > 0:
                        result["failed_rows"] += batch_failed
                        logger.warning(f"{batch_failed} 条记录导入失败")
                    
                    rows_to_import = []
                except Exception as e:
                    session.rollback()
                    error_msg = f"剩余数据导入过程发生错误: {str(e)}"
                    logger.error(error_msg)
                    result["errors"].append(error_msg)
                finally:
                    session.close()
        
        logger.info(f"CSV导入完成，总行数: {result['total_rows']}, 成功: {result['imported_rows']}, 失败: {result['failed_rows']}")
        
    except FileNotFoundError:
        error_msg = f"CSV文件不存在: {csv_file_path}"
        logger.error(error_msg)
        result["errors"].append(error_msg)
    except UnicodeDecodeError:
        error_msg = f"CSV文件编码错误，请确保使用UTF-8编码"
        logger.error(error_msg)
        result["errors"].append(error_msg)
    except Exception as e:
        error_msg = f"导入过程出错: {str(e)}"
        logger.error(error_msg)
        result["errors"].append(error_msg)
    
    return result

def main():
    """
    主函数，用于直接运行脚本
    """
    import argparse
    
    parser = argparse.ArgumentParser(description="导入CSV数据到TowerV1表")
    parser.add_argument("csv_file", help="CSV文件路径")
    args = parser.parse_args()
    
    # 提供默认的CSV文件路径，如果命令行参数没有提供
    csv_file_path = args.csv_file
    
    logger.info("===== CSV数据导入工具 ======")
    logger.info(f"目标CSV文件: {csv_file_path}")
    logger.info(f"目标数据表: TowerV1")
    logger.info("============================")
    
    # 执行导入
    result = import_csv_to_towerv1(csv_file_path)
    
    # 打印导入结果摘要
    logger.info("\n===== 导入结果摘要 =====")
    logger.info(f"📊 总行数: {result['total_rows']}")
    logger.info(f"✅ 成功导入: {result['imported_rows']}")
    logger.info(f"❌ 失败行数: {result['failed_rows']}")
    
    success_rate = 0
    if result['total_rows'] > 0:
        success_rate = (result['imported_rows'] / result['total_rows']) * 100
    logger.info(f"📈 成功率: {success_rate:.2f}%")
    logger.info("=====================")
    
    if result['errors']:
        logger.error(f"\n❌ 发现 {len(result['errors'])} 个错误:")
        for idx, error in enumerate(result['errors'], 1):
            logger.error(f"{idx}. {error}")
    
    logger.info("\n导入操作完成。详细日志已保存到 import_csv.log 文件中。")

# 如果直接运行此脚本，执行main函数
if __name__ == "__main__":
    main()